﻿/*****************************************************************************/
/* Stored Procedure :: DeleteServiceMember                                   */
/*****************************************************************************/
/* Copyright (C) 2008, Perot Systems Corporation. All right reserved.        */
/* Developer: Ed. Mueller (edward.mueller@ps.net)                            */
/* Started:  08/20/2008                                                      */
/*****************************************************************************/
CREATE PROCEDURE [Kernel].[DeleteServiceMember]
@OID bigint
AS 

---------------------------------------------------------------------------------------------------
--						Liberty
---------------------------------------------------------------------------------------------------
--  Stored Procedure :: DeleteServiceMember
--                                                                            
---------------------------------------------------------------------------------------------------

SET NOCOUNT ON

DECLARE @RetCode int,
		@CaseID bigint

BEGIN TRANSACTION

IF (EXISTS (SELECT * FROM dbo.ServiceMen WHERE (OID = @OID)))
BEGIN
	DECLARE CasesCursor CURSOR FOR
		SELECT OID FROM dbo.Cases WHERE (ServiceManID = @OID)
	OPEN CasesCursor 
	FETCH NEXT FROM CasesCursor INTO @CaseID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC @RetCode = CaseManagement.DeleteCase @CaseID
		IF (@RetCode <> 0) Goto ExitError
		FETCH NEXT FROM CasesCursor INTO @CaseID
	END

	CLOSE CasesCursor
	DEALLOCATE CasesCursor
	
	DELETE FROM dbo.Visits
	FROM dbo.Visits v
	INNER JOIN dbo.Cases c ON c.OID = v.CaseID
	WHERE (c.ServiceManID = @OID)
	IF (@@ERROR <> 0) Goto ExitError

	DELETE FROM dbo.Grants
	FROM dbo.Grants g
	INNER JOIN dbo.Cases c ON c.OID = g.CaseID
	WHERE (c.ServiceManID = @OID)
	IF (@@ERROR <> 0) Goto ExitError
	
	DELETE FROM dbo.Cases
	WHERE (ServiceManID = @OID)
	IF (@@ERROR <> 0) Goto ExitError

	DELETE FROM dbo.ServiceHistory WHERE (PersonnelID = @OID)
	IF (@@ERROR <> 0) Goto ExitError
	
	DELETE FROM dbo.ServiceMen WHERE (OID = @OID)
	IF (@@ERROR <> 0) Goto ExitError

	DELETE FROM dbo.People WHERE (OID = @OID)
	IF (@@ERROR <> 0) Goto ExitError
END

-- Normal Exit
IF (@@TRANCOUNT > 0)
BEGIN
	COMMIT TRANSACTION
END
SET @RetCode = 0
GOTO endRoutine              

ExitError:
IF (@@TRANCOUNT > 1)
BEGIN
	COMMIT TRANSACTION
END
ELSE IF (@@TRANCOUNT = 1)
BEGIN
	ROLLBACK TRANSACTION
END
SET @RetCode = 1
GOTO endRoutine              

endRoutine:
RETURN (@RetCode)
GO
